﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ControlPresupuesto
{
    public class SincronizarOrdenesCompra
    {
        private static SincronizarOrdenesCompra sincronizarOrdenesCompra = null;

        public static SincronizarOrdenesCompra get()
        {
            if (SincronizarOrdenesCompra.sincronizarOrdenesCompra == null) { SincronizarOrdenesCompra.sincronizarOrdenesCompra = new SincronizarOrdenesCompra(); }
            return SincronizarOrdenesCompra.sincronizarOrdenesCompra;
        }

        public bool sincronizar(int diasDeAntelacion)
        {
            string xml = string.Empty;

            try
            {               
                    // Obtengo el codigo de la iltima orden de compra registrada en la Base de datos
                    string connectionString1 = System.Configuration.ConfigurationManager.ConnectionStrings[Utils.get().getValorParametro("SeccionAppConfigControlPresupuestoConnectionString")].ConnectionString;
                    DataTable table = new DataTable();
                    using (SqlConnection conn = new System.Data.SqlClient.SqlConnection(connectionString1))
                    {
                        conn.Open();
                        SqlCommand command = conn.CreateCommand();
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "[spOrdenesDeCompra]";
                        command.Parameters.Add(new SqlParameter("@OP", "ObtenerUltimaOC"));
                        command.Parameters.Add(new SqlParameter("@ID", diasDeAntelacion));
                        table.Load(command.ExecuteReader());
                        conn.Close();
                    }
                    
                    /// Obtengo la informacion faltante de infor
                    string connectionString2 = System.Configuration.ConfigurationManager.ConnectionStrings["ControlPresupuesto.Properties.Settings.INFORConnectionString"].ConnectionString;
                    using (System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(connectionString2))
                    {
                        conn.Open();
                        System.Data.Odbc.OdbcCommand command = conn.CreateCommand();
                        
                        command.CommandText = "";
                        command.CommandText += " SELECT ";
                        command.CommandText += "     A0.ORDRPM AS NumOrden,";
                        command.CommandText += "     A0.TRMDPM AS DescripcionOrden,";
                        command.CommandText += "     A0.POSTPM AS EstadoOrden,";
                        command.CommandText += "     A0.DORDPM AS FechaCreacionOrden,";
                        command.CommandText += "     REPLACE(CAST(A0.APVAPM AS VARCHAR(18)),',','.') AS ValorOrden,";
                        command.CommandText += "     C0.VNDRVM AS CodProveedor,";
                        command.CommandText += "     C0.VN35VM AS NombreProveedor,";
                        command.CommandText += "     A0.WHIDPM AS Almacen,";
                        command.CommandText += "     A0.BUYRPM AS CodAnalistaCompra,";
                        command.CommandText += "     TRIM(E0.I1NOP1) || '' AS DESCRIPCION ";
                        command.CommandText += " FROM  ";
                        command.CommandText += "    AMFLIBA.POMASTV0 A0 ";
                        command.CommandText += "    INNER JOIN (SELECT ORDRPT, MIN(PISQPT)  as PISQPT FROM  AMFLIBA.POITEMV3 GROUP BY  ORDRPT) B0";
                        command.CommandText += "        ON A0.ORDRPM=B0.ORDRPT";
                        command.CommandText += "    INNER JOIN AMFLIBA.POITEMV3 B1";
                        command.CommandText += "        ON  B0.ORDRPT=B1.ORDRPT";
                        command.CommandText += "        AND B0.PISQPT=B1.PISQPT";
                        command.CommandText += "    LEFT OUTER JOIN AMFLIBA.VENNAMV0 C0";
                        command.CommandText += "        ON A0.VNDRPM=C0.VNDRVM";
                        command.CommandText += "    INNER JOIN AMFLIBA.ITMRVAV0 D0";
                        command.CommandText += "        ON B1.ITNOPT=D0.ITNOAD";
                        command.CommandText += "    LEFT JOIN AMFLIBA.IMF001 E0";
                        command.CommandText += "        ON D0.STIDAD=E0.I1SITE AND D0.ITNOAD=E0.I1ITEM AND D0.ITRVAD=E0.I1REVI";
                        command.CommandText += " WHERE ";
                        command.CommandText += "    A0.ORDRPM > '" + (table.Rows.Count > 0 ? table.Rows[0][0] : "P000000")+"'";
                        command.CommandTimeout = 0;
                        table = new DataTable();
                        table.Load(command.ExecuteReader());
                        conn.Close();
                    }
                    xml = Utils.get().dataTableToXML(table);

                    // Guardo la nueva informacion obtenida de infor en la base de datos Del proyecto
                    table = new DataTable();
                    using (SqlConnection conn = new System.Data.SqlClient.SqlConnection(connectionString1))
                    {
                        conn.Open();
                        SqlCommand command = conn.CreateCommand();
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "[spOrdenesDeCompra]";
                        command.Parameters.Add(new SqlParameter("@OP", "ImportarOrdenesDeCompra"));
                        command.Parameters.Add(new SqlParameter("@ParamAuxiliar", xml));
                        table.Load(command.ExecuteReader());
                        conn.Close();
                    }                    
            }
            catch (Exception ex)
            {
                MessageException.show(ex);
                return false;
            }
            return true;
        }

    }
}
